﻿using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace Database
{
    public class Database
    {
        public SqlConnection SqlConnection { get; set; }

        public Dictionary<string, int> GetDistribution(string distributionProcedure)
        {
            var distribution = new Dictionary<string, int>();
            var sqlCommand = new SqlCommand(distributionProcedure, SqlConnection) {
                                                                                      CommandType = CommandType.StoredProcedure
                                                                                  };

            SqlConnection.Open();

            var sqlDataReader = sqlCommand.ExecuteReader();
            while (sqlDataReader.Read())
            {
                for (var i = 0; i < sqlDataReader.FieldCount; i++)
                {
                    distribution.Add(sqlDataReader.GetName(i), int.Parse(sqlDataReader[i].ToString()));
                }
            }
            SqlConnection.Close();
            return distribution;
        }

        public Dictionary<string, int> GetGeneralStatistics(int raceFilter, int classFilter, int genderFilter)
        {
            var generalStatistics = new Dictionary<string, int>();
            var sqlCommand = new SqlCommand("GetGeneralStatistics", SqlConnection) {
                                                                                       CommandType = CommandType.StoredProcedure
                                                                                   };
            if(raceFilter>-1)
            {
                sqlCommand.Parameters.Add("@Race", SqlDbType.Int).Value = raceFilter;
            }
            if(classFilter>-1)
            {
                sqlCommand.Parameters.Add("@Class", SqlDbType.Int).Value = classFilter;
            }
            if(genderFilter>-1)
            {
                sqlCommand.Parameters.Add("@Gender", SqlDbType.Int).Value = genderFilter;
            }

            SqlConnection.Open();

            var sqlDataReader = sqlCommand.ExecuteReader();
            while (sqlDataReader.Read())
            {
                for (var i = 0; i < sqlDataReader.FieldCount; i++)
                {
                    string value = sqlDataReader[i].ToString();
                    value = string.IsNullOrEmpty(value) ? "0" : sqlDataReader[i].ToString();
                    generalStatistics.Add(sqlDataReader.GetName(i), int.Parse(value));
                }
            }
            SqlConnection.Close();
            return generalStatistics;
        }

        public int GetClass(string className)
        {
            var sqlCommand = new SqlCommand("GetClass", SqlConnection) {CommandType = CommandType.StoredProcedure};
            sqlCommand.Parameters.Add("@ClassName", SqlDbType.NVarChar).Value = className;
            SqlConnection.Open();

            var classId = int.Parse(sqlCommand.ExecuteScalar().ToString());
            SqlConnection.Close();

            return classId;
        }

        public int GetRace(string raceName)
        {
            var sqlCommand = new SqlCommand("GetRace", SqlConnection) { CommandType = CommandType.StoredProcedure };
            sqlCommand.Parameters.Add("@RaceName", SqlDbType.NVarChar).Value = raceName;
            SqlConnection.Open();

            var raceId = int.Parse(sqlCommand.ExecuteScalar().ToString());
            SqlConnection.Close();

            return raceId;
        }

        public int GetGender(string genderName)
        {
            var sqlCommand = new SqlCommand("GetGender", SqlConnection) { CommandType = CommandType.StoredProcedure };
            sqlCommand.Parameters.Add("@GenderName", SqlDbType.NVarChar).Value = genderName;
            SqlConnection.Open();

            var genderId = int.Parse(sqlCommand.ExecuteScalar().ToString());
            SqlConnection.Close();

            return genderId;
        }
    }
}